--    Author    : ChenErHao
--    Name      : DM.MID_T_MTH_DEP_RATE_D011013.HQL
--    Functions : 专题-当月吸收存款利率
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.MID_T_MTH_DEP_RATE_D011013 PARTITION (DATA_MONTH = '#V_DATA_MONTH#')  
SELECT   --第二层机构统计
         BELONG_AREA_CODE,
         CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
              WHEN B.ORG_LVL_ID_2 ='' THEN '9999'
              ELSE B.ORG_LVL_ID_2 END ORG_LVL_ID_2,
         CUSTOMER_TYPE,
         CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
              WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
              ELSE C.DEPB_CATEGORY_ID_1 END DEPB_CATEGORY_ID_1,
         LOAN_PERIOD_TYPE_ID,  
         nvl(SUM(DEP_AMOUNT),0) DEP_AMOUNT, ---存款发生额
         nvl(SUM(DEP_AMOUNT_AMT),0) DEP_AMOUNT_AMT ---存款发生额加权金额
               FROM (
                    SELECT A.BELONG_AREA_CODE,
                             A.DATA_ORG_CODE,
                            A.CUSTOMER_TYPE,
                            A.PRODUCT_CATEGORY,
                            A.LOAN_PERIOD_TYPE_ID,
                            A.RATE_LEVEL, 
                            A.ACCOUNT_CODE,A.DEP_AGREEMENT_CODE,A.DEP_CUR_TYPE , A.RATE_FIXED_FLOAT , 
                             CASE WHEN NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0) > NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0)
                             THEN (NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0)-NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0))
                             ELSE 0 END + A.T1_DEP_BALANCE_IS_CURRENT_2  DEP_AMOUNT, ---存款发生额
                            CASE WHEN NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0) > NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0)
                             THEN (NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0)-NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0) )
                             ELSE 0 END*A.RATE_LEVEL + NVL(A.T1_DEP_BALANCE_IS_CURRENT_2,0)*A.RATE_LEVEL  DEP_AMOUNT_AMT
                            FROM  
                             (select  
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE , RATE_FIXED_FLOAT   ,
                            sum(t1_DEP_BALANCE_IS_CURRENT_1) t1_DEP_BALANCE_IS_CURRENT_1,sum(t1_DEP_BALANCE_IS_CURRENT_2) t1_DEP_BALANCE_IS_CURRENT_2,
                            sum(t2_DEP_BALANCE_IS_CURRENT_1) t2_DEP_BALANCE_IS_CURRENT_1,sum(t2_DEP_BALANCE_IS_CURRENT_2) t2_DEP_BALANCE_IS_CURRENT_2
                            from (SELECT
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE , RATE_FIXED_FLOAT  ,
                            CASE WHEN PRODUCT_CATEGORY in('D011','D013') AND  DATA_DATE='#V_DATA_DATE#' AND DEP_AGREEMENT_SDATE IS NULL THEN DEP_BALANCE ELSE 0 END  t1_DEP_BALANCE_IS_CURRENT_1, ---活期
                             CASE WHEN PRODUCT_CATEGORY not in('D011','D013')  AND  DATA_DATE='#V_DATA_DATE#' AND concat(substr(DEP_AGREEMENT_SDATE,1,4),substr(DEP_AGREEMENT_SDATE,6,2)) ='#V_DATA_DATE#' THEN DEP_BALANCE ELSE 0 END  t1_DEP_BALANCE_IS_CURRENT_2,  ---当月新产生定期                       
                            CASE WHEN PRODUCT_CATEGORY in('D011','D013') AND  DATA_DATE='#V_LAST_1M_END_DATE#' AND DEP_AGREEMENT_SDATE IS NULL THEN DEP_BALANCE ELSE 0 END  t2_DEP_BALANCE_IS_CURRENT_1, ---活期
                             CASE WHEN PRODUCT_CATEGORY not in('D011','D013')  AND  DATA_DATE='#V_LAST_1M_END_DATE#' AND concat(substr(DEP_AGREEMENT_SDATE,1,4),substr(DEP_AGREEMENT_SDATE,6,2)) ='#V_LAST_1M_END_DATE#' THEN DEP_BALANCE ELSE 0 END  t2_DEP_BALANCE_IS_CURRENT_2  ---当月新产生定期
                             FROM
                               EDW.DEPB A  WHERE
                                   DATA_DATE in('#V_LAST_1M_END_DATE#','#V_DATA_DATE#') 
                                   --and PRODUCT_CATEGORY in('D011','D012','D013','D014') --modify 20180709
                                   ) t group by
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE ,
                             RATE_FIXED_FLOAT  ) A )A
                     LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  ---金融机构
                     LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID ---存款产品类别
                    GROUP BY  
                                   BELONG_AREA_CODE,
                                   CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                                        WHEN B.ORG_LVL_ID_2 ='' THEN '9999'
                                        ELSE B.ORG_LVL_ID_2 END ,
                                   CUSTOMER_TYPE,
                                   CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
                                        WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                                        ELSE C.DEPB_CATEGORY_ID_1 END ,
                                   LOAN_PERIOD_TYPE_ID,RATE_LEVEL
                                   
union all

SELECT   --第三层机构统计
         BELONG_AREA_CODE,
         CASE WHEN B.ORG_LVL_ID_3 IS NULL THEN '9999'
              WHEN B.ORG_LVL_ID_3 ='' THEN '9999'
              ELSE B.ORG_LVL_ID_3 END ORG_LVL_ID_2,
         CUSTOMER_TYPE,
         CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
              WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
              ELSE C.DEPB_CATEGORY_ID_1 END DEPB_CATEGORY_ID_1,
         LOAN_PERIOD_TYPE_ID,  
         nvl(SUM(DEP_AMOUNT),0) DEP_AMOUNT, ---存款发生额
         nvl(SUM(DEP_AMOUNT_AMT),0) DEP_AMOUNT_AMT ---存款发生额加权金额
               FROM (
                    SELECT A.BELONG_AREA_CODE,
                             A.DATA_ORG_CODE,
                            A.CUSTOMER_TYPE,
                            A.PRODUCT_CATEGORY,
                            A.LOAN_PERIOD_TYPE_ID,
                            A.RATE_LEVEL, 
                            A.ACCOUNT_CODE,A.DEP_AGREEMENT_CODE,A.DEP_CUR_TYPE , A.RATE_FIXED_FLOAT , 
                             CASE WHEN NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0) > NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0)
                             THEN (NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0)-NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0))
                             ELSE 0 END + A.T1_DEP_BALANCE_IS_CURRENT_2  DEP_AMOUNT, ---存款发生额
                            CASE WHEN NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0) > NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0)
                             THEN (NVL(A.T1_DEP_BALANCE_IS_CURRENT_1,0)-NVL(A.T2_DEP_BALANCE_IS_CURRENT_1,0) )
                             ELSE 0 END*A.RATE_LEVEL + NVL(A.T1_DEP_BALANCE_IS_CURRENT_2,0)*A.RATE_LEVEL  DEP_AMOUNT_AMT
                            FROM  
                             (select 
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE , RATE_FIXED_FLOAT   ,
                            sum(t1_DEP_BALANCE_IS_CURRENT_1) t1_DEP_BALANCE_IS_CURRENT_1,sum(t1_DEP_BALANCE_IS_CURRENT_2) t1_DEP_BALANCE_IS_CURRENT_2,
                            sum(t2_DEP_BALANCE_IS_CURRENT_1) t2_DEP_BALANCE_IS_CURRENT_1,sum(t2_DEP_BALANCE_IS_CURRENT_2) t2_DEP_BALANCE_IS_CURRENT_2
                            from (SELECT 
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE , RATE_FIXED_FLOAT  ,
                            CASE WHEN PRODUCT_CATEGORY in('D011','D013') AND  DATA_DATE='#V_DATA_DATE#' AND DEP_AGREEMENT_SDATE IS NULL THEN DEP_BALANCE ELSE 0 END  t1_DEP_BALANCE_IS_CURRENT_1, ---活期
                             CASE WHEN PRODUCT_CATEGORY not in('D011','D013')  AND  DATA_DATE='#V_DATA_DATE#' AND concat(substr(DEP_AGREEMENT_SDATE,1,4),substr(DEP_AGREEMENT_SDATE,6,2)) ='#V_DATA_DATE#' THEN DEP_BALANCE ELSE 0 END  t1_DEP_BALANCE_IS_CURRENT_2,  ---当月新产生定期                       
                            CASE WHEN PRODUCT_CATEGORY in('D011','D013') AND  DATA_DATE='#V_LAST_1M_END_DATE#' AND DEP_AGREEMENT_SDATE IS NULL THEN DEP_BALANCE ELSE 0 END  t2_DEP_BALANCE_IS_CURRENT_1, ---活期
                             CASE WHEN PRODUCT_CATEGORY not in('D011','D013')  AND  DATA_DATE='#V_LAST_1M_END_DATE#' AND concat(substr(DEP_AGREEMENT_SDATE,1,4),substr(DEP_AGREEMENT_SDATE,6,2)) ='#V_LAST_1M_END_DATE#' THEN DEP_BALANCE ELSE 0 END  t2_DEP_BALANCE_IS_CURRENT_2  ---当月新产生定期
                             FROM
                               EDW.DEPB A  WHERE
                                   DATA_DATE in('#V_LAST_1M_END_DATE#','#V_DATA_DATE#') 
                                   ) t group by 
                             BELONG_AREA_CODE,
                             DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID,
                            RATE_LEVEL, 
                            ACCOUNT_CODE,DEP_AGREEMENT_CODE,DEP_CUR_TYPE ,
                             RATE_FIXED_FLOAT  ) A )A
                     LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  ---金融机构
                     LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID ---存款产品类别
                    GROUP BY  
                                   BELONG_AREA_CODE,
                                   CASE WHEN B.ORG_LVL_ID_3 IS NULL THEN '9999'
                                        WHEN B.ORG_LVL_ID_3 ='' THEN '9999'
                                        ELSE B.ORG_LVL_ID_3 END ,
                                   CUSTOMER_TYPE,
                                   CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
                                        WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                                        ELSE C.DEPB_CATEGORY_ID_1 END ,
                                   LOAN_PERIOD_TYPE_ID,RATE_LEVEL;                                 